table of contents
ALTER SEQUENCE(7) | PostgreSQL 16.1 Documentation | ALTER SEQUENCE(7) |
NAME¶
ALTER_SEQUENCE - change the definition of a sequence generator
SYNOPSIS¶
ALTER SEQUENCE [ IF EXISTS ] name
[ AS data_type ]
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ]
[ RESTART [ [ WITH ] restart ] ]
[ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ] ALTER SEQUENCE [ IF EXISTS ] name SET { LOGGED | UNLOGGED } ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema
DESCRIPTION¶
ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameters not specifically set in the ALTER SEQUENCE command retain their prior settings.
You must own the sequence to use ALTER SEQUENCE. To change a sequence's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the sequence's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the sequence. However, a superuser can alter ownership of any sequence anyway.)
PARAMETERS¶
name
IF EXISTS
data_type
Changing the data type automatically changes the minimum and maximum values of the sequence if and only if the previous minimum and maximum values were the minimum or maximum value of the old data type (in other words, if the sequence had been created using NO MINVALUE or NO MAXVALUE, implicitly or explicitly). Otherwise, the minimum and maximum values are preserved, unless new values are given as part of the same command. If the minimum and maximum values do not fit into the new data type, an error will be generated.
increment
minvalue
NO MINVALUE
maxvalue
NO MAXVALUE
start
restart
In contrast to a setval call, a RESTART operation on a sequence is transactional and blocks concurrent transactions from obtaining numbers from the same sequence. If that's not the desired mode of operation, setval should be used.
cache
CYCLE
NO CYCLE
SET { LOGGED | UNLOGGED }
OWNED BY table_name.column_name
OWNED BY NONE
new_owner
new_name
new_schema
NOTES¶
ALTER SEQUENCE will not immediately affect nextval results in backends, other than the current one, that have preallocated (cached) sequence values. They will use up all cached values prior to noticing the changed sequence generation parameters. The current backend will be affected immediately.
ALTER SEQUENCE does not affect the currval status for the sequence. (Before PostgreSQL 8.3, it sometimes did.)
ALTER SEQUENCE blocks concurrent nextval, currval, lastval, and setval calls.
For historical reasons, ALTER TABLE can be used with sequences too; but the only variants of ALTER TABLE that are allowed with sequences are equivalent to the forms shown above.
EXAMPLES¶
Restart a sequence called serial, at 105:
ALTER SEQUENCE serial RESTART WITH 105;
COMPATIBILITY¶
ALTER SEQUENCE conforms to the SQL standard, except for the AS, START WITH, OWNED BY, OWNER TO, RENAME TO, and SET SCHEMA clauses, which are PostgreSQL extensions.
SEE ALSO¶
CREATE SEQUENCE (CREATE_SEQUENCE(7)), DROP SEQUENCE (DROP_SEQUENCE(7))
2023 | PostgreSQL 16.1 |